#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
SQL Server 连接脚本
跨平台（macOS/Ubuntu）兼容：优先尝试 pymssql，失败后回退到 pyodbc（ODBC Driver 17/18）。
"""

import os
import platform
import pymssql
try:
    import pyodbc  # 可选：Ubuntu 上推荐安装 msodbcsql17/18 + unixODBC
except Exception:  # 避免因未安装而报 ImportError
    pyodbc = None
import sys
from typing import Optional
from config.config import DATABASE_CONFIG

class SQLServerConnection:
    """SQL Server 数据库连接类"""
    
    def __init__(self, server: str, port: int, database: str, username: str, password: str):
        self.server = server
        self.port = port
        self.database = database
        self.username = username
        self.password = password
        self.connection = None
    
    def connect_with_pymssql(self) -> bool:
        """使用 pymssql + FreeTDS 连接（专为 SQL Server 2014 优化）"""
        # 在 Linux 上，未设置时默认指定 TDS 版本，避免握手不兼容
        if platform.system().lower() == 'linux' and not os.environ.get('TDSVER'):
            os.environ['TDSVER'] = '7.0'

        # 专门针对 SQL Server 2014 的 FreeTDS 配置
        pymssql_configs = [
            # 配置1: FreeTDS 7.0 协议 (SQL Server 2014 最佳选择) ⭐
            {
                "name": "FreeTDS 7.0 (SQL Server 2014 专用)",
                "config": {
                    "server": self.server,
                    "port": self.port,
                    "user": self.username,
                    "password": self.password,
                    "database": self.database,
                    "timeout": 60,
                    "login_timeout": 60,
                    "charset": "UTF-8",
                    "tds_version": "7.0"  # 关键设置：SQL Server 2014 兼容
                }
            },
            # 配置2: FreeTDS 7.3 协议
            {
                "name": "FreeTDS 7.3",
                "config": {
                    "server": self.server,
                    "port": self.port,
                    "user": self.username,
                    "password": self.password,
                    "database": self.database,
                    "timeout": 30,
                    "login_timeout": 30,
                    "charset": "UTF-8",
                    "tds_version": "7.3"
                }
            },
            # 配置3: 基本连接（回退选项）
            {
                "name": "基本连接",
                "config": {
                    "server": self.server,
                    "port": self.port,
                    "user": self.username,
                    "password": self.password,
                    "database": self.database,
                    "timeout": 30,
                    "login_timeout": 30,
                    "charset": 'UTF-8'
                }
            },
            # 配置4: 完整地址连接
            {
                "name": "完整地址连接",
                "config": {
                    "server": f"{self.server}:{self.port}",
                    "user": self.username,
                    "password": self.password,
                    "database": self.database,
                    "timeout": 60,
                    "login_timeout": 60,
                    "charset": 'UTF-8'
                }
            }
        ]
        
        for config in pymssql_configs:
            try:
                print(f"🔄 尝试 pymssql {config['name']}...")
                self.connection = pymssql.connect(**config['config'])
                print(f"✅ 使用 pymssql ({config['name']}) 成功连接到数据库: {self.database}")
                return True
                
            except pymssql.Error as e:
                print(f"❌ pymssql {config['name']} 失败: {e}")
                continue
            except Exception as e:
                print(f"❌ pymssql {config['name']} 错误: {e}")
                continue
        
        print("❌ 所有 pymssql 连接配置都失败了")
        return False

    def connect_with_pyodbc(self) -> bool:
        """使用 pyodbc 连接（ODBC Driver 17/18），适合 Ubuntu 服务器"""
        if pyodbc is None:
            print("⚠️ 未安装 pyodbc，跳过 pyodbc 连接尝试")
            return False

        drivers = DATABASE_CONFIG.get('drivers', []) or [
            'ODBC Driver 18 for SQL Server',
            'ODBC Driver 17 for SQL Server',
            'SQL Server'
        ]

        for driver in drivers:
            # Driver 18 默认加密，需显式信任服务器证书或配置证书链
            encrypt_modes = ['yes'] if '18' in driver else ['yes', 'no']

            for encrypt in encrypt_modes:
                base = (
                    f"DRIVER={{{driver}}};"
                    f"SERVER={self.server},{self.port};"
                    f"DATABASE={self.database};"
                    f"UID={self.username};PWD={self.password};"
                )

                if encrypt == 'yes':
                    base += "Encrypt=yes;TrustServerCertificate=yes;"
                else:
                    base += "Encrypt=no;"

                try:
                    print(f"🔄 尝试 pyodbc {driver} (Encrypt={encrypt})...")
                    self.connection = pyodbc.connect(base, timeout=60)
                    print(f"✅ 使用 pyodbc ({driver}) 成功连接到数据库: {self.database}")
                    return True
                except Exception as e:
                    print(f"❌ pyodbc {driver} (Encrypt={encrypt}) 失败: {e}")
                    continue

        print("❌ 所有 pyodbc 连接配置都失败了")
        return False
    
    def connect(self) -> bool:
        """连接数据库（统一入口）"""
        # 先尝试 pymssql（mac 上验证稳定；Linux 需 FreeTDS/TLS 兼容）
        if self.connect_with_pymssql():
            return True

        # 回退到 pyodbc（Ubuntu 上更通用，需安装 ODBC Driver 17/18）
        print("🔁 回退到 pyodbc 连接...")
        return self.connect_with_pyodbc()
    
    def test_connection(self) -> bool:
        """测试数据库连接"""
        if not self.connection:
            print("❌ 没有有效的数据库连接")
            return False
        
        try:
            cursor = self.connection.cursor()
            cursor.execute("SELECT @@VERSION as version")
            result = cursor.fetchone()
            print(f"🔍 数据库版本: {result[0] if result else 'Unknown'}")
            
            # 获取当前数据库信息
            cursor.execute("SELECT DB_NAME() as current_db")
            result = cursor.fetchone()
            print(f"📍 当前数据库: {result[0] if result else 'Unknown'}")
            
            cursor.close()
            return True
            
        except Exception as e:
            print(f"❌ 测试连接失败: {e}")
            return False
    
    def execute_query(self, query: str) -> Optional[list]:
        """执行查询并返回结果"""
        if not self.connection:
            print("❌ 没有有效的数据库连接")
            return None
        
        try:
            cursor = self.connection.cursor()
            cursor.execute(query)
            
            # 如果是SELECT查询，返回结果
            if query.strip().upper().startswith('SELECT'):
                results = cursor.fetchall()
                columns = [desc[0] for desc in cursor.description]
                cursor.close()
                return [dict(zip(columns, row)) for row in results]
            else:
                # 对于INSERT/UPDATE/DELETE，提交事务
                self.connection.commit()
                cursor.close()
                print("✅ 查询执行成功")
                return []
                
        except Exception as e:
            print(f"❌ 查询执行失败: {e}")
            if self.connection:
                self.connection.rollback()
            return None
    
    def close(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()
            print("🔐 数据库连接已关闭")

def main():
    """主函数"""
    # 数据库连接信息
    SERVER = "fast9.shenzhuo.vip"
    PORT = 56472
    DATABASE = "GP"
    USERNAME = "sa"
    PASSWORD = "shanks00"
    
    print("🔑 使用 pymssql 连接数据库...")
    
    # 创建数据库连接对象
    db = SQLServerConnection(SERVER, PORT, DATABASE, USERNAME, PASSWORD)
    
    # 尝试连接
    print("🔄 尝试连接数据库...")
    connected = db.connect()
    
    if not connected:
        print("❌ 数据库连接失败，请检查网络连接和数据库配置")
        sys.exit(1)
    
    # 测试连接
    print("\n📊 测试数据库连接...")
    if db.test_connection():
        print("✅ 数据库连接测试成功！")
        
        # 示例查询
        print("\n🔍 执行示例查询...")
        
        # 查看所有表
        tables_query = """
        SELECT TABLE_NAME 
        FROM INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_TYPE = 'BASE TABLE'
        ORDER BY TABLE_NAME
        """
        
        results = db.execute_query(tables_query)
        if results:
            print(f"📋 数据库中的表 ({len(results)} 个):")
            for i, table in enumerate(results[:10], 1):  # 只显示前10个表
                print(f"  {i}. {table['TABLE_NAME']}")
            if len(results) > 10:
                print(f"  ... 还有 {len(results) - 10} 个表")
        
        print("\n💡 您现在可以使用 db.execute_query() 执行自定义SQL查询")
        
    else:
        print("❌ 数据库连接测试失败")
    
    # 关闭连接
    db.close()

if __name__ == "__main__":
    main()